from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker

engine = create_engine(
    "mysql+pymysql://root:root@localhost/test?charset=utf8",
    echo=False
)

Base = declarative_base()

# https://www.cnblogs.com/yc-c/p/9627968.html
# https://www.cnblogs.com/ybjourney/p/11832045.html

class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column(String(100))
    age = Column(Integer)

    def __init__(self, name, age):
        self.name = name
        self.age = age


def initDb():
    Base.metadata.create_all(engine)


def dropDb():
    Base.metadata.drop_all(engine)


Session = sessionmaker(bind=engine)
session = Session()

# add_user = User("索隆", '20')
# session.add(add_user)
# session.commit()

# users = session.query(User).filter_by(id=1).all()
# user = session.query(User).filter_by(id=1).first()
# print(users)
# print(user)

# 改
# session.query(User).filter_by(id=2).update({'name', 'Jack'})
#
# session.query(User).filter_by(id=2).update({'age': 21})
# session.commit()
users = session.query(User).filter_by(name='Jack').first()
print(users)
# users.name = 'test'
# session.add(users)
#
# # 单个删除
# deleteUser = session.query(User).filter(User.name == 'test').first()
# if deleteUser:
#     session.delete(deleteUser)
#     session.commit()
#
# # 批量条件删除
# session.query(User).filter(User.name == "test").delete()
# session.commit()